import sys
sys.path.append("../")
from model.CommonDb import *
from services.BaseService import *
from frameworks.utils.FileUtil import *
import pandas as pd
import datetime

class DayKlineService(BaseService):
    def __init__(self):
        super(DayKlineService, self).__init__()
        self.beforTen = (datetime.datetime.now()-datetime.timedelta(days=10)).strftime("%Y-%m-%d")
        self.beforThirty = (datetime.datetime.now() - datetime.timedelta(days=30)).strftime("%Y-%m-%d")

    def getData(self,code):
        orderKey = code[7:8]
        db = CommonDb("day_kline_"+str(orderKey),"sto")
        return db.selectAll("code='"+code+"' order by trade_date asc","*",True)

    def getThirtyData(self,code):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        sql = "select a.close,a.pre_close,a.money,a.pre_money,b.flow_volume from day_kline_" + str(orderKey) + " a left join codes b " \
            " on a.code = b.code where a.code='" + code + "' and a.trade_date > '" + self.beforThirty + "' order by a.trade_date asc"
        return db.executeByFieldMap(sql,["close","pre_close","money","pre_money","flow_volume"])

    def getThirtyDataByEnd(self,code,startDay,endDay):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        sql = "select a.close,a.pre_close,a.money,a.pre_money,b.flow_volume from day_kline_" + str(orderKey) + " a left join codes b " \
            " on a.code = b.code where a.code='" + code + "' " \
            "and a.trade_date between '" + startDay + "' and '" + endDay + "' order by a.trade_date asc"
        return db.executeByFieldMap(sql,["close","pre_close","money","pre_money","flow_volume"])

    def getTenHasZhangtingByDate(self,startDay,endDay):
        self.db = CommonDb("codes", "sto")
        codes = []
        for i in range(10):
            sql = "select b.* from (select distinct code from day_kline_" + str(i) \
                + " where trade_date between '" + startDay + "' and '" + endDay + "' and pre_close != '' " \
                  "and (close-pre_close)/pre_close > 0.093) a left join codes b on a.code=b.code"
            rs = self.db.executeMap(sql)
            for row in rs:
                codes.append(row)
        return codes

    def getTenHasZhangting(self):
        self.db = CommonDb("codes", "sto")
        codes = []
        for i in range(10):
            sql = "select b.* from (select distinct code from day_kline_" + str(i) + " where trade_date > '" + self.beforTen + "' and pre_close != '' " \
                  "and (close-pre_close)/pre_close > 0.093) a left join codes b on a.code=b.code"
            rs = self.db.executeMap(sql)
            for row in rs:
                codes.append(row)
        return codes

    def getDataByDate(self,code,startDay,endDay):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        return db.selectAll("code='"+ code +"' and trade_date between '" + startDay + "' and '" + endDay + "' order by trade_date","*",True)

    def getAllData(self,code):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        return db.selectAll("code='"+ code +"' order by trade_date","*",True)

    def getAllDataArr(self,code):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        return db.selectAll("code='"+ code +"' order by trade_date","*")

    def getLastData(self,code):
        orderKey = code[7:8]
        db = CommonDb("day_kline_" + str(orderKey), "sto")
        return db.selectAll("code='"+ code +"' order by trade_date desc limit 1","*",True)

    def getTradeDay(self):
        db = CommonDb("day_kline_0", "sto")
        sql = "select distinct trade_date from day_kline_0 where code='SH600000' order by trade_date;"
        rs = db.execute(sql)
        allday = []
        for row in rs:
            allday.append(row[0])
        return allday


